CSC 240 Spring 2006 Project – Initial Information

America East Airline wants to update their system to help their employees and customers. We are to develop an integrated database application that will keep track of passengers, flights, tickets, boarding passes, even pilots, planes, and airports.

Functionality provided should include:

· User friendly interface(s) for data input for each kind of data

· User friendly interfaces for update/delete for any kinds of data subject to frequent updates

· Menus leading users to the capabilities that they can make use of

· Support for some pre-planned reports and queries. An initial specification of desirable queries/ reports is given below.

We will talk more about this project in upcoming weeks. Your first task is to hand in a survey to aid me in assigning people to compatible groups. This should be done ASAP (today!). Note that the DB design is due Mar 17, a first installment is due Apr 7, and the completed project is due Apr 28. The will be a handout later on project deliverables.

NOTE: Some (NOT ALL) business rules include:

A Route is a take-off Airport and a landing Airport, not a specific occurrence (e.g from Philly to Boston, no particular day), and no landings in between.
A Flight may involve more than one Routes (e.g. Flight 109 is from Philly to Boston with continuing service to Portland Maine)
A Flight may occur multiple times (probably on some regular schedule such as every day, or Mon/Wed/Fri)
A Flight Segment is a particular occurrence of a Route (e.g. Flight 109 segment from Philly to Boston on 1/19/06)
Tickets typically are sold for multiple Flight Segments – for each Flight Segment that a Passenger flies on they need a separate Boarding Pass (in real life, if you don’t change planes you don’t need a separate boarding pass, but I think this will be a simplifying assumption).
A Passenger can be in different Classes on different Flight Segments (e.g. First Class going to Boston, Coach returning to Philly)(because of upgrades)
A passenger purchases all portions of a trip through a single Sales Channel.
An entire trip is purchased with a single Fare Basis (e.g. if one segment is non-refundable, they all are)
There are two Pilots on each Flight Segment, and we will not differentiate between them, not try to keep track of who is actually “flying” at a given time.
All Planes of a given Model have the same capacity (and seating configuration). This may be a simplifying assumption.
Pilots are certified (trained and qualified) to fly different Plane Models. A Pilot may be qualified on more than one Model.

You will probably have to ask me about other business rules as you get into design. Don’t wait until the last minute to get going.

Tentative Queries / Reports:

1. Show all Flight Segments flown by pilot X, with Date, To (airport), From (airport), Departure Time, Arrival Time, Plane Type (e.g. Boeing 747)

2. Show all Flight Segments flown by plane X, with Date, To (airport), From (airport), Distance (in Miles)

3. Show all Premium Frequent Flyers, with name, City, State, Zip, and Home Airport

4. Show all Passengers on Flight Segment departing from airport X, on Flight Y, on Date Z, with Name, City, State, Zip, Class (e.g. First Class), Fare Basis (e.g. unrestricted, non-changeable), Sales Channel (e.g. our WWW site, travel agent, Travelocity), Frequent Flyer Type, Miles Earned, Price Paid

5. Show all late arriving Flight Segments between Date X and Date Y, with To (airport), From (airport), Date, Minutes Late

6. Show all Routes included in Flight X, with To (airport), From (airport),

7. Show all Flights including a Route From (airport) X To (airport) Y

8. Show all Flight Segments From (airport) X To (airport) Y on Date Z, with Scheduled Departure Time, Scheduled Arrival Time

9. Show all Pilots that are qualified to fly Flight Segment X (based on what Plane has been assigned and Pilot’s qualifications)

10. Show all Flight Segments (scheduled to be) leaving airport X more than 45 minutes and less than 4 hours after flight segment Y is scheduled to arrive airport X on Date Z.

11. Show all Flight Segments current in-transit, with From (airport), To (airport), Flight Number, Scheduled Departure Time, Scheduled Arrival Time

12. Show the Seat Number of Passenger X on Flight Y, departing from Airport Z on Date W.

13. Show the total capacity (seats) on Flight Segments From (airport) X To (airport) Y on Date Z

14. Show the total number of passengers on each Flight Segment, grouped by Dates ascending.

15. Show the total number of passengers on Flight Segment X, grouped by Dates ascending.

16. Show the total number of passengers departing, by airport (From) by Date.

17. Show the total number of Flight Segments flown, by Plane Model.

18. Show the total number of Flight Segments on Date X, by From (airport).

19. Show the total sales revenues by Sales Channel with Purchase Date between X and Y.

20. Show the total sales revenues by Sales Channel with Flight Segment Date between X and Y.

NOTES:

· Always remember when designing output that these are designed to help humans. Thus, for example, if the primary key value is not meaningful to humans, other identifying info should be displayed in addition/instead. E.g. In addition to or instead of Customer ID, display customer name.

· Many of these should be “parameterized” in order to be flexible. Some, but not all, of the queries that should be parameterized are indicated with X, Y, Z etc.

· If the description includes the word “total”, I’m looking for some summarization, not just a list of raw data. The “by” clause is an attempt to indicate what results are to be summarized “by”.

· It probably makes sense to organize your application by what kinds of users need what capabilities. If you have any questions about who might be interested in different queries, please ask.

· All of the above will be queries. Do at least 3 as reports based on their query. Reports are particularly valuable if subtotals or totals or grouping of data is of interest in addition to details.

· Somehow (at least in documentation) let me know which query is which (by number)

· These are tentative. If you find any of these that are problematic, check with me. Also, if you think of any great additional canned queries/reports, feel free to include them.